using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Orders : DataAccess
    {        
        public Orders(string db_str)
        {
            CreateConnection(db_str);
        }
        public DataSet GetTradingBookAcct(string board_id,
           string account_no, string side, string symbol, DateTime from_date, DateTime to_date)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_TradingBookAcct";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@from_date", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@to_date", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 50).Value = account_no;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return order_normal;
        }

        public DataSet GetMatchedOrders(string board_id,
           string account_no, string symbol, DateTime from_date, DateTime to_date)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_MatchedOrders";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@from_date", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@to_date", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 50).Value = account_no;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return order_normal;
        }

        public DataSet GetOrderNormal(DateTime from_date, DateTime to_date, string board_id,
            string user_name, string symbol, string side)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_sel_OrderNormal";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@from_date", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@to_date", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@user_name", SqlDbType.VarChar, 50).Value = user_name;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);                                
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
            return order_normal;
        }
        public DataSet GetOrderInQueue(DateTime tran_date, string board_id,
            string user_name, string symbol, string side)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_sel_OrderInQueue";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@tran_date", SqlDbType.DateTime).Value = tran_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@user_name", SqlDbType.VarChar, 50).Value = user_name;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return order_normal;
        }
        public DataSet GetOrderInQueueX(DateTime tran_date, string board_id,
           string client_id, string symbol, string side)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_OrderInQueue";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@tran_date", SqlDbType.DateTime).Value = tran_date;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;
                cmd.Parameters.Add("@client_id", SqlDbType.VarChar, 20).Value = client_id;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return order_normal;
        }

        public DataSet GetChangeAnCancel(DateTime order_date, string client_id, string symbol)
        {
            DataSet order_normal = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_ChangeAndCancel";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters
                cmd.Parameters.Add("@client_id", SqlDbType.VarChar, 20).Value = client_id;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(order_normal);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return order_normal;
        }

        public int PlaceNewOrder(DateTime order_date, string order_time, string user_name,
            string side, string symbol,decimal volume, string price)
        {
            int result;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_ins_PlaceOrder";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@user_name", SqlDbType.VarChar, 50).Value = user_name;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;
                cmd.Parameters.Add("@volume", SqlDbType.Decimal).Value = volume;
                cmd.Parameters.Add("@price", SqlDbType.VarChar, 10).Value = price;
                cmd.Parameters.Add("@order_date", SqlDbType.DateTime).Value = order_date;
                cmd.Parameters.Add("@order_time", SqlDbType.VarChar, 8).Value = order_time;
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }
        public int PlaceNewOrderX(DateTime order_date, string order_time, string account_no,
            string side, string symbol, decimal volume, string price, string created_by)
        {
            int result;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_PlaceOrder";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 50).Value = account_no;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@side", SqlDbType.VarChar, 1).Value = side;
                cmd.Parameters.Add("@volume", SqlDbType.Decimal).Value = volume;
                cmd.Parameters.Add("@price", SqlDbType.VarChar, 10).Value = price;
                cmd.Parameters.Add("@order_date", SqlDbType.DateTime).Value = order_date;
                cmd.Parameters.Add("@order_time", SqlDbType.VarChar, 8).Value = order_time;
                cmd.Parameters.Add("@date_created", SqlDbType.DateTime).Value = DateTime.Today;
                cmd.Parameters.Add("@created_by", SqlDbType.VarChar, 50).Value = created_by;
                conn.Open();
                result = Convert.ToInt16(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public int CancelOrderX(string order_number, string created_by)
        {
            int result;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_CancelOrderX";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 50).Value = order_number;
                cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 50).Value = created_by;
                conn.Open();
                result = Convert.ToInt16(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public int ChangeOrderX(string order_number, decimal new_price, string created_by)
        {
            int result;
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_CancelOrderX";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 50).Value = order_number;
                cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 50).Value = created_by;
                cmd.Parameters.Add("@NewPrice", SqlDbType.Decimal).Value = new_price;
                conn.Open();
                result = Convert.ToInt16(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }
    }
}
